﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;

namespace PGMapper
{
    public static class MigrationExtensions
    {
        public static void MigrateFromClasses(this PostgresDatabase db)
        {
            db.MigrateFromAssemblies(Assembly.GetCallingAssembly());
        }

        public static void MigrateFromAssemblies(this PostgresDatabase db, params Assembly[] assemblies)
        {
            db.MigrateSchema(
                PostgresSchema.FromDatabase(db),
                PostgresSchema.FromAssemblies(assemblies));
        }
    }

    static class PostgresDatabaseModificationExtensions
    {
        public static void MigrateSchema(this PostgresDatabase db, PostgresSchema dbSchema, PostgresSchema classSchema)
        {
            db.MigrateSchema(dbSchema, classSchema, false);
        }
            
        public static void MigrateSchema(this PostgresDatabase db, PostgresSchema dbSchema, PostgresSchema classSchema, bool drop)
        {
            // order is important here.

            // drop tables from the DB that are no longer mapped
            foreach (PostgresTable table in dbSchema.Tables.Except(classSchema.Tables))
            {
                if (drop)
                {
                    // remove any constraints for other tables that reference this table to match CASCADE behavior
                    dbSchema.Tables.ForEach(t => t.Constraints.RemoveAll(c => c.ReferencedTableName == table.TableName));
                    db.DropTable(table);
                }
                else
                    Console.WriteLine("found unmapped table " + table.TableName);
            }

            // create new tables that are needed
            foreach (PostgresTable table in classSchema.Tables.Except(dbSchema.Tables))
            {
                db.CreateTable(table);
                dbSchema.Tables.Add(new PostgresTable { TableName = table.TableName });
            }

            // compare columns for each table
            foreach (PostgresTable table in classSchema.Tables)
                db.MigrateColumns(dbSchema.Tables.First(t => t.TableName == table.TableName), table, dbSchema, drop);

            // compare primary keys for each table
            foreach (PostgresTable table in classSchema.Tables)
                db.MigratePrimaryKeys(dbSchema.Tables.First(t => t.TableName == table.TableName), table, dbSchema, drop);

            // compare constraints for each table
            foreach (PostgresTable table in classSchema.Tables)
                db.MigrateConstraints(dbSchema.Tables.First(t => t.TableName == table.TableName), table, dbSchema, drop);
        }

        public static void MigrateColumns(this PostgresDatabase db, PostgresTable dbTable, 
            PostgresTable classTable, PostgresSchema dbSchema, bool drop)
        {
            IEnumerable<PostgresColumn> columnsToDrop = dbTable.Columns.Except(classTable.Columns);
            IEnumerable<PostgresColumn> columnsToAdd = classTable.Columns.Except(dbTable.Columns);

            // drop columns that are no longer mapped
            foreach (PostgresColumn column in columnsToDrop)
            {
                if (drop || columnsToAdd.Any(columnToAdd => columnToAdd.ColumnName == column.ColumnName)) // force drop if we're going to add it later
                {
                    // update all constraints to reflect what postgres will do automatically via CASCADE
                    dbSchema.Tables.ForEach(t => t.Constraints.RemoveAll(c => c.ReferencedTableName == column.TableName && c.ColumnName == column.ColumnName));
                    dbTable.Constraints.RemoveAll(c => c.ColumnName == column.ColumnName);
                    db.DropColumn(column);
                }
                else
                    Console.WriteLine("found unmapped column {0}.{1}", column.TableName, column.ColumnName);
            }

            // add new columns that are needed
            foreach (PostgresColumn column in columnsToAdd)
                db.AddColumn(column);
        }

        public static void MigratePrimaryKeys(this PostgresDatabase db, PostgresTable dbTable,
            PostgresTable classTable, PostgresSchema dbSchema, bool drop)
        {
            var dbPKeyConstraints = dbTable.Constraints
                .FindAll(c => c.ConstraintType == ConstraintType.PrimaryKey)
                .OrderBy(c => c.OrdinalPosition);

            var classPKeyConstraints = classTable.Constraints
                .FindAll(c => c.ConstraintType == ConstraintType.PrimaryKey)
                .OrderBy(c => c.OrdinalPosition);

            string dbPrimaryKey = string.Join(",", dbPKeyConstraints.Select(c => c.ColumnName).ToArray());
            string classPrimaryKey = string.Join(",", classPKeyConstraints.Select(c => c.ColumnName).ToArray());

            if (dbPrimaryKey != classPrimaryKey)
            {
                // drop old primary key if it still exists
                if (!string.IsNullOrEmpty(dbPrimaryKey))
                    if (drop)
                        db.DropConstraint(dbPKeyConstraints.First());
                    else
                        Console.WriteLine("found unmapped primary key '{0}' on table {1}", dbPrimaryKey, dbTable.TableName);

                // add new primary key
                db.AddPrimaryKey(dbTable, classPrimaryKey);
            }
        }

        public static void MigrateConstraints(this PostgresDatabase db, PostgresTable dbTable,
            PostgresTable classTable, PostgresSchema dbSchema, bool drop)
        {
            // migrate column constraints
            var dbConstraints = dbTable.Constraints.FindAll(c => c.ConstraintType != ConstraintType.PrimaryKey);
            var classConstraints = classTable.Constraints.FindAll(c => c.ConstraintType != ConstraintType.PrimaryKey);

            foreach (PostgresConstraint constraint in dbConstraints.Except(classConstraints))
                if (drop)
                    db.DropConstraint(constraint);
                else
                    Console.WriteLine("found unmapped constraint {0}.{1}", dbTable.TableName, constraint.ConstraintName);

            foreach (PostgresConstraint constraint in classConstraints.Except(dbConstraints))
                if (constraint.ConstraintType == ConstraintType.Unique)
                    db.AddUniqueConstraint(constraint);
                else if (constraint.ConstraintType == ConstraintType.ForeignKey)
                    db.AddForeignKey(constraint);
        }

        public static void CreateTable(this PostgresDatabase db, PostgresTable table)
        {
            Console.WriteLine("Creating table " + table);
            db.Execute("create table " + table.TableName + " ()");
        }

        public static void DropTable(this PostgresDatabase db, PostgresTable table)
        {
            Console.WriteLine("Dropping table " + table);
            db.Execute("drop table " + table.TableName + " cascade");
        }

        public static void AddColumn(this PostgresDatabase db, PostgresColumn column)
        {
            Console.WriteLine("Adding column " + column);
            db.Execute("alter table " + column.TableName + " add column " + column.ColumnName + " "
                + column.GetDataTypeCreationSpecifier());
        }

        public static void DropColumn(this PostgresDatabase db, PostgresColumn column)
        {
            Console.WriteLine("Dropping column " + column);
            db.Execute("alter table " + column.TableName + " drop column " + column.ColumnName + " cascade");
        }

        public static void AddPrimaryKey(this PostgresDatabase db, PostgresTable table, string pkey)
        {
            Console.WriteLine("Creating new primary key ({0}) for {1}", pkey, table);
            db.Execute("alter table " + table.TableName + " add primary key (" + pkey + ")");
        }

        public static void AddForeignKey(this PostgresDatabase db, PostgresConstraint constraint)
        {
            Console.WriteLine("Creating foreign key column {0} ({1}) referencing {2} ({3})", 
                constraint.TableName, constraint.ColumnName, 
                constraint.ReferencedTableName, constraint.ReferencedColumnName);
            db.Execute("alter table " + constraint.TableName + " add foreign key (" + constraint.ColumnName
                + ") references " + constraint.ReferencedTableName + " (" + constraint.ReferencedColumnName + ")");
        }

        public static void AddUniqueConstraint(this PostgresDatabase db, PostgresConstraint constraint)
        {
            Console.WriteLine("Creating unique constraint for {0}", constraint.ColumnName);
            db.Execute("alter table " + constraint.TableName + " add unique (" + constraint.ColumnName + ")");
        }

        public static void DropConstraint(this PostgresDatabase db, PostgresConstraint constraint)
        {
            Console.WriteLine("Dropping constraint " + constraint);
            db.Execute("alter table " + constraint.TableName + " drop constraint " + constraint.ConstraintName);
        }
    }
}
